﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Xml;
using System.Xml.XPath;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Reflection;

namespace Workshop
{
    public partial class FormCourseinformation : Form
    {
        private String courseID;
        public FormCourseinformation()
        {
            InitializeComponent();
        }

        private void splitContainer1_Panel2_Paint(object sender, PaintEventArgs e)
        {

        }


        //this function is used when we want to merge cells
        public void Merge(Microsoft.Office.Interop.Excel.Range range, String value)
        {
            //To merge the cells
            range.Merge(range.MergeCells);
            //clear the value of the cell
            range.Value2 = Type.Missing;
            //write the data you want to input 
            range.Value2 = value;
            //make the font in the center of the cell
            range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            //draw the frame
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            //the size of font
            range.Font.Size = 12;
        }

        public void ExportToExcel(DataSet ds, String Name)
        {
            if (ds == null)
                return;

            //create excel, including workbooks and worksheet
            Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            ExcelApp.Application.Workbooks.Add(true);
            ExcelApp.Application.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel.Worksheet Sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[1];
            //create data table
            System.Data.DataTable dt = ds.Tables[0];
            System.Data.DataTable dt1 = ds.Tables[1];
            System.Data.DataTable dt2 = ds.Tables[2];
            System.Data.DataTable dt3 = ds.Tables[3];

            int countrow = 1;   //count the row of the table
            //it is used when we need another table following previous table

            //title "Courses"
            Microsoft.Office.Interop.Excel.Range TitleRange1 = Sheet1.get_Range(Sheet1.Cells[1, 1], Sheet1.Cells[1, 4]);
            Merge(TitleRange1, "Courses");


            //the first table
            //get the range of cell
            var RowAll = Sheet1.get_Range(Sheet1.Cells[2, 1], Sheet1.Cells[(dt.Rows.Count) + 3, 4]);
            //make font stand in the middle of cell
            RowAll.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            //the font size
            RowAll.Font.Size = 11;
            //draw frame in the range area
            RowAll.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;


            //this is the header of the first table
            for (int a = 0; a < dt.Columns.Count; a++)
            {
                ExcelApp.Cells[2, a + 1] = dt.Columns[a].ColumnName;
            }

            countrow++;

            //title "Major Courses"
            //get the range of cell we need to merge
            Microsoft.Office.Interop.Excel.Range TitleRange2 = Sheet1.get_Range(Sheet1.Cells[3, 1], Sheet1.Cells[3, 4]);
            //call the Merge function 
            Merge(TitleRange2, "Major Courses");

            //this is the data in the first table
            for (int a = 0; a < dt.Rows.Count; a++)
            {

                for (int b = 0; b < dt.Columns.Count; b++)
                {
                    //write data from dataset one by one
                    ExcelApp.Cells[a + 4, b + 1] = dt.Rows[a][b].ToString();

                }
                //plus 1 when finish writing a row
                countrow++;

            }

            //title "GE Required Courses"
            Microsoft.Office.Interop.Excel.Range TitleRange5 = Sheet1.get_Range(Sheet1.Cells[countrow + 2, 1], Sheet1.Cells[countrow + 2, 4]);
            //call the Merge function
            Merge(TitleRange5, "GE Required Courses");

            //the fouth table
            //their functions are same as we told before
            var RowAll3 = Sheet1.get_Range(Sheet1.Cells[countrow + 3, 1], Sheet1.Cells[countrow + (dt3.Rows.Count) + 2, 4]);
            RowAll3.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            RowAll3.Font.Size = 11;
            RowAll3.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            //this is the data of the fouth table
            for (int a = 0; a < dt3.Rows.Count; a++)
            {

                for (int b = 0; b < dt3.Columns.Count; b++)
                {
                    ExcelApp.Cells[countrow + 3, b + 1] = dt3.Rows[a][b].ToString();
                }
                countrow++;
            }


            //title "Workload"
            Microsoft.Office.Interop.Excel.Range TitleRange3 = Sheet1.get_Range(Sheet1.Cells[2, 6], Sheet1.Cells[2, 7]);
            Merge(TitleRange3, "Workload");


            //the second table 
            //their functions are same as we told before
            var RowAll1 = Sheet1.get_Range(Sheet1.Cells[3, 6], Sheet1.Cells[(dt1.Rows.Count) + 3, 7]);
            RowAll1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            RowAll1.Font.Size = 11;
            RowAll1.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            double workload;

            //this is the header of the second table
            for (int i = 0; i < dt1.Columns.Count; i++)
            {
                Sheet1.Cells[3, i + 6] = dt1.Columns[i].ColumnName;
            }

            //this is the data of the second table
            for (int i = 0; i < dt1.Rows.Count; i++)
            {
                for (int j = 0; j < dt1.Columns.Count; j++)
                {
                    Sheet1.Cells[i + 4, j + 6] = dt1.Rows[i][j].ToString();
                    //here we need to get the workload of each teacher
                    if (j == 1)
                    {
                        //change the data to double, then we can campare them 
                        double.TryParse(dt1.Rows[i][j].ToString(), out workload);
                        //we check if the workload is equal to 3 or greater than 3 
                        if (workload >= 3)
                        {
                            //if workload >= 3, then we get the area of the cells and highlight them
                            Microsoft.Office.Interop.Excel.Range range = Sheet1.Range[Sheet1.Cells[i + 4, j + 5], Sheet1.Cells[i + 4, j + 6]];
                            range.Interior.ColorIndex = 35;     //color
                        }
                    }
                }

            }

            //title "IT"
            Microsoft.Office.Interop.Excel.Range TitleRange4 = Sheet1.get_Range(Sheet1.Cells[2, 9], Sheet1.Cells[2, 10]);
            Merge(TitleRange4, "IT");


            //the third table
            //their functions are same as we told before
            var RowAll2 = Sheet1.get_Range(Sheet1.Cells[3, 9], Sheet1.Cells[(dt2.Rows.Count) + 3, 10]);
            RowAll2.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            RowAll2.Font.Size = 10;
            RowAll2.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            //this is the header of the third table
            for (int a = 0; a < dt2.Columns.Count; a++)
            {
                ExcelApp.Cells[3, a + 9] = dt2.Columns[a].ColumnName;
            }

            //this is the data of the third table
            for (int a = 0; a < dt2.Rows.Count; a++)
            {
                for (int b = 0; b < dt2.Columns.Count; b++)
                {
                    ExcelApp.Cells[a + 4, b + 9] = dt2.Rows[a][b].ToString();

                }

            }

            ExcelApp.Cells.EntireColumn.AutoFit();  //make the cell fit to the font
            ExcelApp.Cells.Font.Name = "Georgia";   //the font


            try
            {
                //never display the alerts when it covers the previous file
                ExcelApp.DisplayAlerts = false;
                //save
                ExcelApp.ActiveWorkbook.Saved = false;
                ExcelApp.ActiveWorkbook.SaveAs(Name, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel5, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
                MessageBox.Show("create TeachingAssignment.xls success!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                //show the error, for example, the file is openning, then it can not be saved
                MessageBox.Show("error: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                ExcelApp.Quit();

            }
        }

        public static void SetIDWid(DataGridView dataGridView1)
        {
            for (int i = 0; i < dataGridView1.Columns.Count; i++)
            {
                if (dataGridView1.Columns[i].HeaderCell.Value.ToString() == "ID")
                {
                    dataGridView1.Columns[i].Width = 70;
                    dataGridView1.Columns[i].Resizable = DataGridViewTriState.False;
                    break;
                }
            }
        }

        private void export_Click_1(object sender, System.EventArgs e)
        {
            XmlDocument doc = new XmlDocument();
            //read the xml
            doc.Load("Course.xml");
            //the path
            String path = "//Course";
            //this is the name and path to save this file
            String fileName = "D:\\TeachingAssignment.xls";
            //create a data table
            System.Data.DataTable dt = new System.Data.DataTable();
            //create many data column, this is the header of the data table
            DataColumn col0 = new DataColumn("ID", typeof(string));
            DataColumn col1 = new DataColumn("Subject", typeof(string));
            DataColumn col2 = new DataColumn("Instructor", typeof(string));
            DataColumn col3 = new DataColumn("Students", typeof(string));
            //add them into the data table
            dt.Columns.Add(col0);
            dt.Columns.Add(col1);
            dt.Columns.Add(col2);
            dt.Columns.Add(col3);

            //create a data table
            System.Data.DataTable dt4 = new System.Data.DataTable();
            //create many data column, this is the header of the data table
            DataColumn c0 = new DataColumn("ID", typeof(string));
            DataColumn c1 = new DataColumn("Subject", typeof(string));
            DataColumn c2 = new DataColumn("Instructor", typeof(string));
            DataColumn c3 = new DataColumn("Students", typeof(string));
            //add them into the data table
            dt4.Columns.Add(c0);
            dt4.Columns.Add(c1);
            dt4.Columns.Add(c2);
            dt4.Columns.Add(c3);

            //create a list to save data that read from xml
            XmlNodeList nodes = doc.SelectNodes(path);
            for (int i = 0; i < nodes.Count; i++)
            {
                //if the course category is Major Course
                if (nodes[i].ChildNodes[2].InnerText.ToString() == "Major Course")
                {
                    //create data row to save different form a row
                    DataRow dr = dt.NewRow();
                    //these are the data we need to show on Excel
                    dr[0] = nodes[i].ChildNodes[0].InnerText;
                    dr[1] = nodes[i].ChildNodes[1].InnerText;
                    dr[2] = nodes[i].ChildNodes[6].Attributes[0].Value.ToString();
                    dr[3] = nodes[i].ChildNodes[3].InnerText;
                    //add a data row to data table
                    dt.Rows.Add(dr);
                }

                //if the course category is GE 
                else if (nodes[i].ChildNodes[2].InnerText.ToString() == "GE Course")
                {
                    //create data row to save different form a row
                    DataRow dr4 = dt4.NewRow();
                    //these are the data we need to show on Excel
                    dr4[0] = nodes[i].ChildNodes[0].InnerText;
                    dr4[1] = nodes[i].ChildNodes[1].InnerText;
                    dr4[2] = nodes[i].ChildNodes[6].Attributes[0].Value.ToString();
                    dr4[3] = nodes[i].ChildNodes[3].InnerText;
                    dt4.Rows.Add(dr4);  //add them into data row
                }

                //if the course category is not GE or Major Course, we do not thing
                else
                {

                }
            }


            XmlDocument doc2 = new XmlDocument();
            //read the xml
            doc2.Load("Staff.xml");
            //the path
            String path2 = "//InstructorID";
            XmlNodeList instructors = doc2.SelectNodes(path2);
            //create a data table to save data
            System.Data.DataTable dt2 = new System.Data.DataTable();
            //create many data column, this is the header of the data table
            DataColumn column1 = new DataColumn("Instructor", typeof(string));
            DataColumn column2 = new DataColumn("Workload", typeof(string));
            //add them into data table
            dt2.Columns.Add(column1);
            dt2.Columns.Add(column2);
            for (int j = 0; j < instructors.Count; j++)
            {
                //create data row to save different form a row
                DataRow dr2 = dt2.NewRow();
                //these are the data we need to show on Excel
                dr2[0] = instructors[j].Attributes[0].Value.ToString();
                dr2[1] = instructors[j].Attributes[1].Value.ToString();
                //add a data row to data table
                dt2.Rows.Add(dr2);
            }


            String path3 = "//Courses/ITCourse/InstructorID";
            XmlNodeList ItGroups = doc.SelectNodes(path3);
            //create a data table to save data
            System.Data.DataTable dt3 = new System.Data.DataTable();
            //create many data column, this is the header of the data table
            DataColumn colIns = new DataColumn("Instructor", typeof(string));
            DataColumn colGroup = new DataColumn("Groups", typeof(string));
            dt3.Columns.Add(colIns);
            dt3.Columns.Add(colGroup);
            for (int i = 0; i < ItGroups.Count; i++)
            {
                DataRow dr3 = dt3.NewRow();
                //these are the data we need to show on Excel
                dr3[0] = ItGroups[i].Attributes[0].Value.ToString();
                dr3[1] = ItGroups[i].Attributes[1].Value.ToString();
                dt3.Rows.Add(dr3);
            }

            //create a dataset to save those data table
            DataSet ds = new DataSet();
            ds.Tables.Add(dt);
            ds.Tables.Add(dt2);
            ds.Tables.Add(dt3);
            ds.Tables.Add(dt4);
            //call the function to display data in Excel
            ExportToExcel(ds, fileName);
        }


        private void FormCourseinformation_Load(object sender, EventArgs e)
        {
            XmlDocument xml = new XmlDocument();
            xml.Load("Course.xml");     //load the Course.xml to use
            String path = "//Course/CourseID";      //select them by courseid 
            System.Data.DataTable table = new System.Data.DataTable();
            XmlNodeList nodeList = xml.SelectNodes(path);
            DataColumn column1 = new DataColumn("ID", typeof(String));      //use data table to print the necessary information out
            DataColumn column2 = new DataColumn("Course", typeof(String));
            table.Columns.Add(column1);
            table.Columns.Add(column2);
            for (int i = 0; i < nodeList.Count; i++)
            {
                DataRow row = table.NewRow();
                row[0] = nodeList[i].InnerText;     //this col print the ID of course
                row[1] = nodeList[i].ParentNode.ChildNodes[1].InnerText;        //this col print the name of course
                table.Rows.Add(row);
            }
            dataGridView1.DataSource = table;       //show the table
            this.dataGridView1.Columns["ID"].Visible = false;
            //SetIDWid(dataGridView1);
        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex == -1)
            {
                return;     //if use click the wrong place, do not do anything
            }
            String courseid = dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString();     //get the id from the line which user has chosen
            XmlDocument xml = new XmlDocument();
            xml.Load("Course.xml");
            String path = "//Course/CourseID";
            XmlNodeList nodeList = xml.SelectNodes(path);       //search all the course id 
            for (int i = 0; i < nodeList.Count; i++)
            {
                if (nodeList[i].InnerText == courseid)      //compare with the courseid to find which course's information user want to see
                {
                    labelCourse.Text = nodeList[i].ParentNode.ChildNodes[1].InnerText;      // print out the information in the label
                    labelInstructor.Text = nodeList[i].ParentNode.ChildNodes[6].Attributes[0].Value;
                    labelStudent.Text = nodeList[i].ParentNode.ChildNodes[3].InnerText;
                    labelCategory.Text = nodeList[i].ParentNode.ChildNodes[2].InnerText;
                    courseID = courseid;        //store the course id in a global variable because it is useful
                    break;
                }
            }
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            FormAddcourse addcourse = new FormAddcourse();      //jump to the add page 
            addcourse.Show();
            this.Hide();
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            XmlDocument xml2 = new XmlDocument();
            xml2.Load("Course.xml");
            XmlNodeList nodelist = xml2.SelectNodes("//CourseID");
            for (int i = 0; i < nodelist.Count; i++)
            {
                if (nodelist[i].InnerText == courseID)
                {
                    if (nodelist[i].ParentNode.ChildNodes[4].InnerText != "" || nodelist[i].ParentNode.ChildNodes[5].InnerText != "")
                    {
                        MessageBox.Show("You need to reset the TA!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        return;
                    }
                }
            }
                if (labelInstructor.Text == "")
                {
                    XmlDocument xml = new XmlDocument();
                    xml.Load("Course.xml");
                    XmlNodeList delete = xml.SelectNodes("//Course/CourseID");
                    for (int i = 0; i < delete.Count; i++)
                    {
                        if (delete[i].InnerText == courseID)        //when user choose a course, courseid will be saved in courseID
                        {
                            delete[i].ParentNode.ParentNode.RemoveChild(delete[i].ParentNode);      //delete the all node by this statement
                            xml.Save("Course.xml");
                            update();       //every time it delete some course from Course.xml, it will refresh the whole page in case of bug occuring
                            break;
                        }
                    }
                }
                else
                {
                    MessageBox.Show("You need to reset the instructor first!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
        }

        //this is for update the data table on the left hand side
        public void update()   
        {
            XmlDocument xml = new XmlDocument();
            xml.Load("Course.xml");
            String path = "//Course/CourseID";
            System.Data.DataTable table = new System.Data.DataTable();
            XmlNodeList nodeList = xml.SelectNodes(path);
            DataColumn column1 = new DataColumn("ID", typeof(String));
            DataColumn column2 = new DataColumn("Course", typeof(String));
            table.Columns.Add(column1);
            table.Columns.Add(column2);
            for (int i = 0; i < nodeList.Count; i++)
            {
                DataRow row = table.NewRow();
                row[0] = nodeList[i].InnerText;     //refresh the data table
                row[1] = nodeList[i].ParentNode.ChildNodes[1].InnerText;    //refresh the data table
                table.Rows.Add(row);
            }
            dataGridView1.DataSource = table;
            this.dataGridView1.Columns["ID"].Visible = false;
            labelCategory.Text = null;      // the text in the label need to be refreshed
            labelCourse.Text = null;
            labelInstructor.Text = null;
            labelStudent.Text = null;
        }

        private void tAArrangementToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Form1 form1 = new Form1();      //jump to Coursearrangement page
            form1.Show();
            this.Hide();
        }

        private void courseTimeToolStripMenuItem_Click(object sender, EventArgs e)
        {
            FormITgroups form = new FormITgroups();     //jump to Coursetime page
            form.Show();
            this.Hide();
        }

        private void instructorInfomationToolStripMenuItem_Click(object sender, EventArgs e)
        {
            FormAddinstructor form = new FormAddinstructor();   //jump to addinstructor page
            form.Show();
            this.Hide();
        }

        private void labelInstructor_Click(object sender, EventArgs e)
        {

        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void menuStrip1_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {

        }

        private void pictureBox1_Click(object sender, EventArgs e)
        {
            FormHeadPixel headPixel = new FormHeadPixel();
            headPixel.Show();
            this.Close();
        }

        private void labelCategory_Click(object sender, EventArgs e)
        {

        }

        private void labelCourse_Click(object sender, EventArgs e)
        {

        }

        private void labelStudent_Click(object sender, EventArgs e)
        {

        }
    }
}
